﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace XWG.JWBST.JWBSTDAL
{
    public class TbBstOnlineAskStatisticsDAL
    {
        /// <summary>
        /// 根据时间点获取在线咨询点击量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public static DataTable GetAskNumByDD(string datetime)
        {
            string sql = "select count(1) SL,t.CATEGORY,D.DESCRIPTION from TB_BST_ONLINE_ASK t inner join tb_bst_dic d on t.category=d.code where parentnode='JZ' and to_char(t.createdate,'yyyy-mm-dd') = :time group by t.CATEGORY,D.DESCRIPTION ";
            OracleParameter[] sp = { 
                                    new OracleParameter(":time",datetime)
                                   };
            return OrclHelper.GetDT(sql, sp);
        }

        /// <summary>
        /// 根据时间段获取在线咨询点击量
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public static DataTable GetAskNumByDate(string starttime, string endtime)
        {
            string sql = " select count(1) SL,t.CATEGORY,D.DESCRIPTION from TB_BST_ONLINE_ASK t inner join tb_bst_dic d on t.category=d.code where parentnode='JZ' and to_char(t.createdate,'yyyy-mm-dd') between :starttime and :endtime group by t.CATEGORY,D.DESCRIPTION ";
            OracleParameter[] sp = { 
                                   new OracleParameter(":starttime",starttime),
                                   new OracleParameter(":endtime",endtime)
                                   };
            return OrclHelper.GetDT(sql, sp);
        }

        /// <summary>
        /// 根据月份和警种获取在线咨询点击量
        /// </summary>
        /// <param name="dateMM"></param>
        /// <returns></returns>
        public static DataTable GetAskNumByMM(string dateMM)
        {
            string sql = "select count(1) SL,t.CATEGORY,D.DESCRIPTION from TB_BST_ONLINE_ASK t inner join tb_bst_dic d on t.category=d.code where parentnode='JZ' and to_char(t.createdate,'yyyy-mm') = :time group by t.CATEGORY,D.DESCRIPTION ";
            OracleParameter[] sp = { 
                                   new OracleParameter(":time",dateMM)
                                    };
            return OrclHelper.GetDT(sql, sp);
        }
    }
}
